package models;

import java.util.*;
import javax.persistence.*;

import play.db.ebean.*;
import play.data.format.*;
import play.data.validation.*;
import com.avaje.ebean.Ebean;
import com.avaje.ebean.Query;
import com.avaje.ebean.RawSql;
import com.avaje.ebean.RawSqlBuilder;
import com.avaje.ebean.SqlQuery;
import com.avaje.ebean.SqlRow;
import com.avaje.ebean.validation.NotNull;


/**
 * User entity managed by Ebean
 */
@Entity
@Table(name="appointment_history")
public class AppointmentHistory extends Model {

	public static final long serialVersionUID = 2L;

	@Id
	@Column(name="idAppointment_History")
	public long id;

	/*@Column(name="idPatient")
	public long patientId;*/

	 @OneToOne
	   @JoinColumn(name="idPatient")
	   public User patient;
	 
	 
/*	@Column(name="idDoctor")
	public long doctorId;
*/

	 @OneToOne
	   @JoinColumn(name="idDoctor")
	   public User doctor;
	 
	 @OneToOne
	   @JoinColumn(name="reference_doctor_id")
	   public User referringDoctor;
	 
	 @Formats.DateTime(pattern="MM/dd/yyyy")
	@Column(name="date_of_appointment")
    public Date dateOfAppointment;
	 
    @Constraints.Required
    @Column(name="time_of_appointment")
    public String appointmentTime;

    @Column(name="appointment_duration")
    public String appointmentDuration ="00:30";

    @Column(name="appointment_headline")
    public String appointmentHeadline;
    
    @Column(name="appointment_confirmation")
    public String appointmentConfirmation;

	@Column(name="appointment_comment")
    public String appointmentComment;

    @OneToOne
    @JoinColumn(name="idHospital")
    public Hospitals hospital;
	
    @Column(name="created")
    public String created=null;

    @Transient
    public String appointmentEndTime=null;

    @Column(name="payment_status")
    public String paymentStatus;
    
    @Column(name="payment_amount")
    public Double amountPaid;

    @Column(name="doctor_comments")
    public String doctorComments;

    @Column(name="attended")
    public String attended;
    
    @Column(name="video_conference")
    public String video;

    @Column(name="session_id")
    public String sessionId;

    @Column(name="token_id")
    public String tokenId;

    @Column(name="lab_id")
    public Long labId;
    
	@Column(name="updated_on")
    public Date updatedOn;
	
	@OneToOne
    @JoinColumn(name="problem_id")
    public DoctorsReferralTemplates problem;
    // -- Queries

    public static Model.Finder<String,AppointmentHistory> find = new Model.Finder(String.class, AppointmentHistory.class);

    /**
     * Get Appointments for the Doctor.
     */
    public static List<AppointmentHistory> getAppointments(long doctorId,long hospitalId) {
    	if(hospitalId > 0){
	    	 return find
	     	.where()
	         .eq("idDoctor", doctorId)
	      //   .eq("idHospital", hospitalId)
	         .findList();
    	} 
        return find
            	.where()
                .eq("idDoctor", doctorId)
                .findList();

    }


    /**
     * Get Appointments for the Patient.
     */
    public static List<AppointmentHistory> getMyAppointments(long myId,long hospitalId) {
    	if(hospitalId >0){
            return find.where()
                    .eq("idDoctor", myId)
                   //  .eq("idHospital", hospitalId)
                    .findList();

    	}
        return find.where()
            .eq("idPatient", myId)
            .findList();
    }

    /**
     * Get Appointments for the Patient.
     */
    public static AppointmentHistory getAppointmentDetails (long id) {
        return find
				.fetch("patient")
				.fetch("doctor")
        		.where()
            .eq("idAppointment_History", id)
            .findUnique();
    }

    public static List<AppointmentHistory> getAppointmentDetailsForFeed (long id,String userType,long labId) {
    	List<AppointmentHistory> appointments = new ArrayList<AppointmentHistory>();
    	if("D".equals(userType)){
    		appointments =find
    					.fetch("hospital")
    					.fetch("patient")
    					.fetch("doctor")
    					.where()
    					.eq("idDoctor", id)
    					.orderBy("date_of_appointment desc")
    					.findList();
    	}else if("P".equals(userType)){
      		appointments =find
					.fetch("hospital")
					.fetch("patient")
					.fetch("doctor")
					.fetch("problem")
					.where()
					.eq("idPatient", id)
					.orderBy("date_of_appointment desc")
					.findList();
    	}else if("L".equals(userType) && labId>0){
    		appointments =find
					.fetch("hospital")					
					.fetch("doctor")
					.where()
					.eq("lab_Id", labId)
					.orderBy("t0.updated_on desc")
					.findList();
    	}
         return appointments;
    }


    public static List<String> getExistingDoctors (long id) {
    	List<AppointmentHistory> appointments = new ArrayList<AppointmentHistory>();
    	String sql = "select distinct user.name, user.id from user user left outer join appointment_history aph on user.id =aph.idDoctor where idPatient=:id order by date_of_appointment desc";
    	SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
    	sqlQuery.setParameter("id", id);
    	List<SqlRow> rows = sqlQuery.findList();
    	List<String> names= new ArrayList<String>(rows.size());
        for (SqlRow row : rows){
        	names.add(row.getString("name"));
        }

/*      		appointments =find
					.fetch("doctor")
					.where()
					.eq("idPatient", id)
					.orderBy("date_of_appointment desc")
					.findList();
*/         return names;
    }

    public static List<DoctorEarning> getDoctorEarning (long id) {
    	List<DoctorEarning> doctorEarnings = new ArrayList<DoctorEarning>();
    	DoctorEarning doctor = new DoctorEarning();
    	String sql = "SELECT sum(payment_amount) as amount,DATE_FORMAT(date_of_appointment,'%b') as month, year(date_of_appointment) as year FROM appointment_history where idDoctor = :id and YEAR(date_of_appointment) = YEAR(CURDATE())  group by year(date_of_appointment), month(date_of_appointment) order by  year(date_of_appointment), month(date_of_appointment) ";
    	SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
    	sqlQuery.setParameter("id", id);
    	List<SqlRow> rows = sqlQuery.findList();
    	List<String> names= new ArrayList<String>(rows.size());
        for (SqlRow row : rows){
        	doctor.amount = row.getDouble("amount");
        	doctor.month = row.getString("month");
        	doctor.year = row.getLong("year");
        	doctorEarnings.add(doctor);
        	doctor= new DoctorEarning();
        }

         return doctorEarnings;
    }

    public static List<AppointmentHistory> getMonthEarnings (long userId, long month) {
    	Calendar now = Calendar.getInstance()  ;
        return find
        		.fetch("patient")
        		.where()
        		.eq("idDoctor",userId)
        		.eq("month(date_of_appointment)", month)
        		.eq("year(date_of_appointment)", now.get(Calendar.YEAR))
        		.findList();
    }
    
    public static List<AppointmentHistory> getUnattended () {
        return find
        		.fetch("patient")
        		.where()
        		.isNull("attended")
        		.orderBy("payment_amount asc ,date_of_appointment desc ")
        		.findList();
    }
}

